--Aliasing the colums
SELECT ss.name AS SchemaName,
TableName = st.name,
st.object_id ObjectId
FROM sys.schemas AS ss
JOIN sys.tables st
ON ss.schema_id = st.schema_id
ORDER BY SchemaName, TableName;


use [AdventureWorks2012]
go

SELECT s.PurchaseOrderNumber
FROM Sales.SalesOrderHeader s
WHERE EXISTS ( SELECT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE UnitPrice BETWEEN 1900 AND 2000
AND SalesOrderID = s.SalesOrderID );

SELECT DISTINCT sh.PurchaseOrderNumber
FROM Sales.SalesOrderHeader AS sh
JOIN Sales.SalesOrderDetail AS sd
ON sh.SalesOrderID = sd.SalesOrderID
WHERE sd.UnitPrice BETWEEN 1900 AND 2000;



SELECT DISTINCT
s.PurchaseOrderNumber
FROM Sales.SalesOrderHeader s
INNER JOIN (SELECT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE UnitPrice BETWEEN 1900 AND 2000
) as dt
ON s.SalesOrderID = dt.SalesOrderID;


SELECT FirstName,
LastName
FROM Person.Person
TABLESAMPLE SYSTEM (2 PERCENT);





SELECT 
	ShiftName,
	Production,
	Engineering,
	Marketing
FROM 
		(SELECT 
			s.Name AS ShiftName,
			h.BusinessEntityID,
			d.Name AS DepartmentName
		FROM 
			HumanResources.EmployeeDepartmentHistory h
		INNER JOIN 
			HumanResources.Department d
		ON 
			h.DepartmentID = d.DepartmentID
		INNER JOIN 
			HumanResources.Shift s
		ON 
			h.ShiftID = s.ShiftID
		WHERE 
			EndDate IS NULL
		AND d.Name IN ('Production', 'Engineering', 'Marketing')
		) AS a
PIVOT
(
COUNT(BusinessEntityID) FOR DepartmentName IN ([Production], [Engineering], [Marketing])
) AS b
ORDER BY ShiftName;



/*****
FROM table_source
PIVOT ( aggregate_function ( value_column )
FOR pivot_column
IN ( <column_list>)
) table_alias

***/


SELECT s.Name AS ShiftName,
SUM(CASE WHEN d.Name = 'Production' THEN 1 ELSE 0 END) AS Production,
SUM(CASE WHEN d.Name = 'Engineering' THEN 1 ELSE 0 END) AS Engineering,
SUM(CASE WHEN d.Name = 'Marketing' THEN 1 ELSE 0 END) AS Marketing
FROM HumanResources.EmployeeDepartmentHistory h
INNER JOIN HumanResources.Department d
ON h.DepartmentID = d.DepartmentID
INNER JOIN HumanResources.Shift s
ON h.ShiftID = s.ShiftID
WHERE h.EndDate IS NULL
AND d.Name IN ('Production', 'Engineering', 'Marketing')
GROUP BY s.Name;


SELECT StudentId,
CHECKSUM_AGG(Grade) AS GradeChecksumAgg
FROM (VALUES (1, 100),
(1, 100),
(1, 100),
(1, 99),
(1, 99),
(1, 98),
(1, 98),
(1, 95),
(1, 95),
(1, 90)
) dt (StudentId, Grade)
GROUP BY StudentID;



SELECT VAR(TaxAmt) AS Variance_Sample,
VARP(TaxAmt) AS Variance_EntirePopulation
FROM Sales.SalesOrderHeader;

-- Windowed Aggregate Functions
IF OBJECT_ID('tempdb..#Transactions') IS NOT NULL 
   DROP TABLE #Transactions;
CREATE TABLE #Transactions
       (
        AccountId INTEGER,
        TranDate DATE,
        TranAmt NUMERIC(8, 2)
       );
INSERT  INTO #Transactions
SELECT  *
FROM    ( VALUES ( 1, '2011-01-01', 500), 
                 ( 1, '2011-01-15', 50),
                 ( 1, '2011-01-22', 250), 
                 ( 1, '2011-01-24', 75),
                 ( 1, '2011-01-26', 125), 
                 ( 1, '2011-01-28', 175),
                 ( 2, '2011-01-01', 500), 
                 ( 2, '2011-01-15', 50),
                 ( 2, '2011-01-22', 25), 
                 ( 2, '2011-01-23', 125),
                 ( 2, '2011-01-26', 200), 
                 ( 2, '2011-01-29', 250),
                 ( 3, '2011-01-01', 500), 
                 ( 3, '2011-01-15', 50 ),
                 ( 3, '2011-01-22', 5000), 
                 ( 3, '2011-01-25', 550),
                 ( 3, '2011-01-27', 95 ), 
                 ( 3, '2011-01-30', 2500) 
        ) dt (AccountId, TranDate, TranAmt);

SELECT  AccountId,
        TranDate,
        TranAmt,
       -- running total of all transactions
        RunTotalAmt = SUM(TranAmt) OVER (PARTITION BY AccountId ORDER BY TranDate)
FROM    #Transactions AS t
ORDER BY AccountId,
        TranDate;
GO


SELECT  AccountId,
        TranDate,
        TranAmt,
       -- running average of all transactions
        RunAvg = AVG(TranAmt) 
                 OVER (PARTITION BY AccountId 
                           ORDER BY TranDate),
       -- running total # of transactions
        RunTranQty = COUNT(*) 
                     OVER (PARTITION BY AccountId 
                               ORDER BY TranDate),
       -- smallest of the transactions so far
        RunSmallAmt = MIN(TranAmt) 
                      OVER (PARTITION BY AccountId 
                                ORDER BY TranDate),
       -- largest of the transactions so far
        RunLargeAmt = MAX(TranAmt) 
                      OVER (PARTITION BY AccountId 
                                ORDER BY TranDate),
       -- running total of all transactions
        RunTotalAmt = SUM(TranAmt) 
                      OVER (PARTITION BY AccountId 
                                ORDER BY TranDate)
FROM    #Transactions AS t
ORDER BY AccountId,
        TranDate;
GO




-- 7-9. Calculating Totals Based Upon a Subset of Rows
SELECT AccountId ,
       TranDate ,
       TranAmt,
       -- average of the current and previous 2 transactions
       SlideAvg      = AVG(TranAmt) 
                       OVER (PARTITION BY AccountId
                                 ORDER BY TranDate
                                  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),
       -- total # of the current and previous 2 transactions
       SlideQty  = COUNT(*)     
                    OVER (PARTITION BY AccountId 
                              ORDER BY TranDate
                               ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),
       -- smallest of the current and previous 2 transactions
       SlideMin = MIN(TranAmt) 
                  OVER (PARTITION BY AccountId 
                            ORDER BY TranDate
                             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),
       -- largest of the current and previous 2 transactions
       SlideMax = MAX(TranAmt) 
                  OVER (PARTITION BY AccountId 
                            ORDER BY TranDate
                             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),
       -- total of the current and previous 2 transactions
       SlideTotal = SUM(TranAmt) 
                    OVER (PARTITION BY AccountId 
                              ORDER BY TranDate
                               ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
FROM    #Transactions AS t
ORDER BY AccountId,
        TranDate;
GO



-- 7-10. Using a Logical Window
DECLARE @Test TABLE
        (
         RowID INT IDENTITY,
         FName VARCHAR(20),
         Salary SMALLINT
        );

INSERT INTO @Test (FName, Salary)
VALUES ('George',       800),
       ('Sam',          950),
       ('Diane',       1100),
       ('Nicholas',    1250),
       ('Samuel',      1250),
       ('Patricia',    1300),
       ('Brian',       1500),
       ('Thomas',      1600),
       ('Fran',        2450),
       ('Debbie',      2850),
       ('Mark',        2975),
       ('James',       3000),
       ('Cynthia',     3000),
       ('Christopher', 5000);
Select 
	RowID,
	Fname,
	Salary,
	rank() over (order by Salary DESC) as "Rank"
From
	@test	

SELECT RowID,
       FName,
       Salary,
       SumByRows  = SUM(Salary) 
                    OVER (ORDER BY Salary 
                           ROWS UNBOUNDED PRECEDING),
       SumByRange = SUM(Salary) 
                    OVER (ORDER BY Salary 
                          RANGE UNBOUNDED PRECEDING)
FROM   @Test
ORDER BY RowID;
GO



SELECT TOP (10)
AccountNumber,
OrderDate,
TotalDue,
ROW_NUMBER() OVER (PARTITION BY AccountNumber ORDER BY OrderDate) AS RN
FROM Sales.SalesOrderHeader
ORDER BY AccountNumber;



WITH
TENS (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
THOUSANDS (N) AS (SELECT 1 FROM TENS t1 CROSS JOIN TENS t2 CROSS JOIN TENS t3),
MILLIONS (N) AS (SELECT 1 FROM THOUSANDS t1 CROSS JOIN THOUSANDS t2),
TALLY (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM MILLIONS)
SELECT N
FROM TALLY;




DECLARE @RFID_Location TABLE (
    TagId INTEGER,
    Location VARCHAR(25),
    SensorDate DATETIME);
INSERT INTO @RFID_Location
        (TagId, Location, SensorDate)
VALUES  (1, 'Room1', '2012-01-10T08:00:01'),
        (1, 'Room1', '2012-01-10T08:18:32'),
        (1, 'Room2', '2012-01-10T08:25:42'),
        (1, 'Room3', '2012-01-10T09:52:48'),
        (1, 'Room2', '2012-01-10T10:05:22'),
        (1, 'Room3', '2012-01-10T11:22:15'),
        (1, 'Room4', '2012-01-10T14:18:58'),
        (2, 'Room1', '2012-01-10T08:32:18'),
        (2, 'Room1', '2012-01-10T08:51:53'),
        (2, 'Room2', '2012-01-10T09:22:09'),
        (2, 'Room1', '2012-01-10T09:42:17'),
        (2, 'Room1', '2012-01-10T09:59:16'),
        (2, 'Room2', '2012-01-10T10:35:18'),
        (2, 'Room3', '2012-01-10T11:18:42'),
        (2, 'Room4', '2012-01-10T15:22:18');

WITH cte AS
(
SELECT TagId, Location, SensorDate,
       ROW_NUMBER() 
       OVER (PARTITION BY TagId 
                 ORDER BY SensorDate) - 
       ROW_NUMBER()
       OVER (PARTITION BY TagId, Location
                 ORDER BY SensorDate) AS Grp
FROM   @RFID_Location
)
SELECT TagId, Location, SensorDate, Grp,
       DENSE_RANK()
       OVER (PARTITION BY TagId, Location
                 ORDER BY Grp) AS TripNbr
FROM   cte
ORDER BY TagId, SensorDate;

WITH cte AS
(
SELECT TagId, Location, SensorDate,
       ROW_NUMBER() 
       OVER (PARTITION BY TagId 
                 ORDER BY SensorDate) AS RN1,
       ROW_NUMBER()
       OVER (PARTITION BY TagId, Location
                 ORDER BY SensorDate) AS RN2
FROM   @RFID_Location
)
SELECT TagId, Location, SensorDate, 
       RN1, RN2, RN1-RN2 AS Grp
FROM   cte
ORDER BY TagId, SensorDate;

WITH cte AS
(
SELECT TagId, Location, SensorDate,
       ROW_NUMBER() 
       OVER (PARTITION BY TagId 
                 ORDER BY SensorDate) - 
       ROW_NUMBER()
       OVER (PARTITION BY TagId, Location
                 ORDER BY SensorDate) AS Grp
FROM   @RFID_Location
)
SELECT TagId, Location, SensorDate, Grp,
       DENSE_RANK()
       OVER (PARTITION BY TagId, Location
                 ORDER BY Grp) AS TripNbr,
       RANK()
       OVER (PARTITION BY TagId, Location
                 ORDER BY Grp) AS TripNbrRank
FROM   cte
ORDER BY TagId, SensorDate;
GO

-- 7-16. Accessing Values from Other Rows
WITH cte AS
(
SELECT  DATEPART(QUARTER, OrderDate) AS Qtr,
        DATEPART(YEAR, OrderDate) AS Yr,
        TotalDue
FROM    Sales.SalesOrderHeader
), cteAgg AS
(
SELECT  Yr,
        Qtr,
        SUM(TotalDue) AS TotalDue
FROM    cte
GROUP BY Yr, Qtr
)
SELECT  Yr,
        Qtr,
        TotalDue,
        TotalDue - LAG(TotalDue, 1, NULL) 
                   OVER (ORDER BY Yr, Qtr) AS DeltaPriorQtr,
        TotalDue - LAG(TotalDue, 4, NULL) 
                   OVER (ORDER BY Yr, Qtr) AS DeltaPriorYrQtr
FROM    cteAgg
ORDER BY Yr, Qtr;

-- determine the gaps in a column
DECLARE @Gaps TABLE (col1 int PRIMARY KEY CLUSTERED);
 
INSERT INTO @Gaps (col1)
VALUES (1), (2), (3),
       (50), (51), (52), (53), (54), (55),
       (100), (101), (102),
       (500),
       (950), (951), (952),
       (954);

-- Compare the value of the current row to the next row. 
-- If > 1, then there is a gap.
WITH cte AS
(
SELECT  col1 AS CurrentRow,
        LEAD(col1, 1, NULL)
        OVER (ORDER BY col1) AS NextRow
FROM    @Gaps
)
SELECT  cte.CurrentRow + 1 AS [Start of Gap],
        cte.NextRow - 1 AS [End of Gap]
FROM    cte
WHERE   cte.NextRow - cte.CurrentRow > 1;
GO















